package com.bot.rf.sql.util;

import com.bot.rf.entity.BookInfo;
import com.bot.rf.excel.BookExcel;
import com.bot.rf.util.DateUtil;
import com.bot.rf.util.FileUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.bot.rf.config.UserConfig.*;
import static com.bot.rf.util.MyLogger.LOGGER;
import static com.bot.rf.util.MyLogger.getTrace;

/**
 * @program: report-form-demo
 * @description:
 * @author: zyy
 * @create: 2021-02-26 17:09
 **/
public class SqlServerUtil {
    /**
     * 数据库连接、查询、结果集
     */
    private static Connection conn = null;
    private static Statement stmt = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet resultSet = null;
    private static int nums = 0;

    //--------------------------- 用户SqlServer配置 start -----------------------------
    /**
     * url格式  jdbc:sqlserver://127.0.0.1:1433;DatabaseName=uwl
     */
    private static String URL = "jdbc:jtds:sqlserver://" + USER_DATABASE_HOST + ":" +
            USER_DATABASE_PORT + ";DatabaseName=" + USER_DATABASE_NAME;
    //private static String URL = "jdbc:jtds:sqlserver://192.168.2.211:1433;DatabaseName=Dblibrary";
    /**
     * 表名
     */
    private static String TABLE_NAME = USER_MAIN_TABLE_NAME;
    //private static String TABLE_NAME = "RFID_UID1";

    /**
     * 账号和密码
     */
    private static String USER_NAME = USER_DATABASE_USERNAME;
    private static String PASSWORD = USER_DATABASE_PASSWORD;
    //private static String USER_NAME = "sa";
    //private static String PASSWORD = "";
    //--------------------------- 用户SqlServer配置  end  -----------------------------

    //--------------------------- 配置方法 start -------------------------------
    /**
     * 创建连接
     *
     * @return
     */
    public static boolean createOrConnect() {
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            LOGGER.info("连接成功...");
            nums = 0;
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
            LOGGER.info("URL：" + URL);
            if (nums < 3){
                nums ++;
                return createOrConnect();
            }
            return false;
        }
        return true;
    }

    /**
     * 关闭自动提交
     * @return
     */
    public static boolean closeAutoCommit() {
        try {
            if (conn == null || conn.isClosed()) {
                createOrConnect();
            }
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 提交
     * @return
     */
    public static boolean commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 创建 Statement
     * @return
     */
    public static boolean createStatement() {
        try {
            if (stmt != null && !stmt.isClosed()) {
                return true;
            }
            System.out.println("Sql Statement关闭，重新连接...");
            stmt = conn.createStatement();
        } catch (SQLException e) {
            LOGGER.error(getTrace(e));
            return false;
        }
        return true;
    }

    /**
     * 关闭连接
     */
    public static void closeConnect() {
        try {
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }
    //--------------------------- 配置方法  end  -------------------------------

    /**
     * 1、查询全部
     */
    public static List<BookInfo> queryAllBook() {
        // 创建连接
        LOGGER.info("创建连接");
        createOrConnect();
        // 创建Statement
        createStatement();
        String sql = "SELECT * FROM " + TABLE_NAME + " ";
        List<BookInfo> list = new ArrayList<>();
        try {
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                BookInfo bookInfo = new BookInfo();
                String tagId = resultSet.getString("uid");
                String bookName = resultSet.getString("BookName");
                String bookIndex = resultSet.getString("BookIndex");
                String barcode = resultSet.getString("Barcode");
                String ordinaryPosition = resultSet.getString("OrdinaryPosition");
                bookInfo.setTagId(tagId);
                bookInfo.setBarcode(barcode);
                bookInfo.setBookName(bookName);
                bookInfo.setBookIndex(bookIndex);
                bookInfo.setTemporaryLocationName(ordinaryPosition);
                if (!ordinaryPosition.equals("0")){
                    list.add(bookInfo);
                }

            }
            resultSet.close();
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
            return null;
        }
        return list;
    }

    /**
     * 2、批量更新本地库
     *    replace 存在则更新，否则插入
     *
     * @return
     */
    public static void batchUpdateBookInfo(List<BookExcel> bookExcelList){
        // 创建连接
        createOrConnect();
        // 关闭自动提交（事务）
        closeAutoCommit();
        String sql = "update "+ TABLE_NAME +" set list_txm = ?, intime = ? where txm = ? ";
        try {
            pstmt = conn.prepareStatement(sql);
            // 批量插入
            for (BookExcel bookInfo:bookExcelList) {
                pstmt.setString(1, bookInfo.getShelf());
                pstmt.setString(2, DateUtil.getFormatTime());
                pstmt.setString(3, bookInfo.getBarcode());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            commit();
            closeConnect();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }

}
